<?php

class Application_Model_Atendimento extends Zend_Db_Table_Abstract {

    protected $_name = 'atendimento';
    protected $_primary = 'codigo';
    private static $instance;

    public static function getInstance() {
        if (!isset(self::$instance)) {
            self::$instance = new Application_Model_Atendimento();
            return self::$instance;
        }
        return self::$instance;
    }

    public function insert($data) {
        $data = $this->preparaData($data);
        return parent::insert($data);
    }

    public function update(array $data, $where = '') {
        $where = 'codigo = ' . $data['codigo'];
        $data = $this->preparaData($data);
        parent::update($data, $where);
    }

    public function getById($id) {
        return $this->fetchRow("codigo = " . $id . " and dt_exclusao is null");
    }
    
    public function getAtendDetalhe($id){
        $sql = 'select a.*,p.nome as nome_clie
                from atendimento a
                join pessoa p on p.codigo = a.paciente
                where a.codigo = '.$id;
        return $this->getAdapter()->query($sql)->fetchAll();
    }
    
    public function lista($dtInicio,$dtFim,$segmento) {
        $sql = 'select  a.codigo,
                        a.dt_atendimento,
                        a.segmento,
                        a.hora,
                        a.funcionario,
                        a.status,
                        a.dt_exclusao,
                        p.nome as nome_funcionario,
                        cli.nome as nome_cliente, 
                        DATE_FORMAT(a.dt_atendimento , \'%d/%m/%Y\') as data_atend,
                        DATE_FORMAT(a.dt_faturamento , \'%d/%m/%Y\') as dt_faturamento
                from atendimento a
                left join pessoa p on p.codigo = a.funcionario
                left join pessoa cli on cli.codigo = a.paciente
                where a.dt_exclusao is null
                and a.segmento = '.$segmento.'
                and a.dt_atendimento between \''.$dtInicio.'\' and \''.$dtFim.'\' ';

            $sql .= ' order by a.hora,a.dt_atendimento,a.funcionario';
        $rs = $this->getAdapter()->query($sql)->fetchAll();
        return $rs;
    }

    public function getMaxAtendDiaHora($dtInicio, $dtFinal, $segmento){
        $sql = 'select 
                    distinct DATE_FORMAT(dt_atendimento , \'%d/%m/%Y\') as dia,hora,
                    count(*) as quantidade
                from atendimento
                where dt_atendimento between \''.$dtInicio.'\' and \''.$dtFinal.'\'
                and dt_exclusao is null
                and segmento = '.$segmento.'
                group by dt_atendimento,funcionario
                having count(hora) > 3;';
        return $this->getAdapter()->query($sql)->fetchAll();
    }
    
    public function getMaxFuncDiaHora($dtInicial, $dtFinal, $segmento){
        $sql = '
                select
                (
                    select
                    count(distinct aa.funcionario)
                    from atendimento aa
                    where aa.hora  = a.hora
                    and aa.dt_atendimento = a.dt_atendimento
                    and aa.segmento = a.segmento
                ) func_dia
                from atendimento a
                where a.dt_atendimento between \''.$dtInicial.'\' and \''.$dtFinal.'\'
                and a.segmento = '.$segmento.'
                and a.dt_exclusao is null
                having func_dia > 3
                order by func_dia desc
                limit 1;';
        return $this->getAdapter()->query($sql)->fetchAll();
    }
    
    function maximoAntHora($dtInicio, $dtFinal, $segmento){
      /*  $sql = 'select
                  count(distinct funcionario) qnt,a.hora
                from atendimento a
                where  a.hora in ( select g.hora from segmento_agenda g where g.segmento = a.segmento )
                and a.dt_atendimento between \''.$dtInicial.'\' and \''.$dtFinal.'\'
                and a.dt_exclusao is null
                and a.segmento = '.$segmento.'
                group by hora
                having qnt > 3
                order by qnt';*/
        $sql ='select
                count(distinct funcionario) as qnt,hora
                from atendimento
                where dt_atendimento between \''.$dtInicio.'\' and \''.$dtFinal.'\'
                and dt_exclusao is null
                and segmento = '.$segmento.'
                group by hora
                having count(distinct funcionario)  > 3';
         return $this->getAdapter()->query($sql)->fetchAll();
    }
    
    
    public function remove($id) {
        $atendimento = $this->getById($id)->toArray();
        $where = ' codigo = ' . $id;
        $atendimento['dt_exclusao'] = Zend_Date::now()->get('Y/M/d');
        parent::update($atendimento, $where);
    }

    
    private function preparaData($data){
        if($data['codigo'] == 0){
            unset($data['codigo']);
        }
        
        if($data['plano_saude'] == 0){
            unset($data['plano_saude']);
        }
        
        unset($data['nome_clie']);
        return $data;
    }
}